﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Oracle.DataAccess.Client;
namespace Project01_DBSecurity
{
    public partial class Table_Resource : Form
    {
        public Table_Resource()
        {
            InitializeComponent();
        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            dgw_table.Rows.Add();
        }

     

        private void btn_Cancle_table_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void bt_ok_table_Click(object sender, EventArgs e)
        {
            string table_name = "Create table " +  txb_table.Text;
            int count = dgw_table.Rows.Count;
            string column_name = "";
            string datatype = "";
            string primary_key = "";
            string not_null = "";
            string size = "";
            string strsql = table_name + " \n ( \n";
            MessageBox.Show(count.ToString());
            for (int i = 0; i < count - 2; i++)
            {
                column_name = dgw_table.Rows[i].Cells[0].Value.ToString();
                datatype = dgw_table.Rows[i].Cells[1].Value.ToString();
                size = dgw_table.Rows[i].Cells[2].Value.ToString();
                if (Convert.ToBoolean(dgw_table[3, i].Value) == true)
                    not_null = " not null ";
                if (Convert.ToBoolean(dgw_table[4, i].Value) == true)
                    primary_key = " Primary key ";

               
                strsql += column_name + " " + datatype + "(" + size + ")" + " " +  not_null + " " + primary_key + ",\n";

                column_name = "";
                datatype = "";
                primary_key = "";
                 not_null = "";
                 size = "";
            }

            column_name = dgw_table.Rows[count - 2].Cells[0].Value.ToString();
            datatype = dgw_table.Rows[count - 2].Cells[count -2].Value.ToString();
            size = dgw_table.Rows[count - 2].Cells[2].Value.ToString();
            if (Convert.ToBoolean(dgw_table[3, count - 2].Value) == true)
                not_null = " not null ";
            if (Convert.ToBoolean(dgw_table[4, count - 2].Value) == true)
                primary_key = " Primary key ";


            strsql += column_name + " " + datatype + "(" + size + ")" + " " + not_null + " " + primary_key ;
            strsql   = strsql +  "\n)"; 
            MessageBox.Show(strsql);
                try
                {
                    OracleCommand comm = new OracleCommand();
                    comm.Connection = orcl.conn;
                    comm.CommandText = strsql;
                    comm.ExecuteNonQuery();
                    MessageBox.Show("Succeed!", "Anouncement", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

        }

        private void btn_drop_table_Click(object sender, EventArgs e)
        {
            string table_name = cb_table_drop.Text;
            try
            {
                OracleCommand comm = new OracleCommand();
                comm.Connection = orcl.conn;
                comm.CommandText = " Drop table " + table_name;
                comm.ExecuteNonQuery();

                comm.CommandText = "select TABLE_NAME from user_tables ";
                DataSet table = new DataSet();
                OracleDataAdapter data1 = new OracleDataAdapter(comm);
                data1.Fill(table);
                cb_table_drop.DataSource = table.Tables[0];
                cb_table_drop.DisplayMember = "TABLE_NAME";
                MessageBox.Show("Succeed!", "Anouncement", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btn_cancle_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btn_Ok_Click(object sender, EventArgs e)
        {
            string constraint_name = tb_constraint.Text;
            string from_table = cb_constraint.Text;
            string ref_table = cb_constraint_ref.Text;
            string from_column = tb_from_column.Text;
            string ref_column = tb_ref_column.Text;
            string str_sql = "alter table " + from_table + " " + " add constraint " + constraint_name + " foreign key(" + from_column + ")  references " + ref_table + "(" + ref_column + ")";
          
            try
            {
                OracleCommand comm = new OracleCommand();
                comm.Connection = orcl.conn;
                comm.CommandText = str_sql;
                comm.ExecuteNonQuery();

                
                MessageBox.Show("Succeed!", "Anouncement", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void btn_drop_constraint_Click(object sender, EventArgs e)
        {
            string table_name = cb_drop_constraint.Text;
            string constraint_name = tb_drop_constraint.Text;

            try
            {
                OracleCommand comm = new OracleCommand();
                comm.Connection = orcl.conn;
                comm.CommandText = "Alter table " + table_name + " drop constraint " + constraint_name ;
                comm.ExecuteNonQuery();

                
                MessageBox.Show("Succeed!", "Anouncement", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void txb_table_TextChanged(object sender, EventArgs e)
        {
           
        }

        private void cb_edit_table_SelectedIndexChanged(object sender, EventArgs e)
        {
            string table_name = cb_edit_table.Text;

            try
            {
                OracleCommand comm = new OracleCommand();
                comm.Connection = orcl.conn;
                comm.CommandText = "SELECT  COLUMN_NAME, DATA_TYPE, CHAR_LENGTH,NULLABLE FROM user_TAB_COLUMNS WHERE TABLE_NAME = '" + table_name + "'";
                DataSet data = new DataSet();
                OracleDataAdapter edit_Table = new OracleDataAdapter(comm);
                edit_Table.Fill(data);
                dgw_edit_table.DataSource = data.Tables[0];
                data.Dispose();
                comm.Dispose();
                edit_Table.Dispose();


                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void cb_edit_table_SelectedValueChanged(object sender, EventArgs e)
        {

        }

        private void btn_edit_cancle_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btn_edit_ok_Click(object sender, EventArgs e)
        {

        }


       
    }
}
